﻿using Entity;
using Entity.DbEntity;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace BLL.Base
{
    public class PubMethod
    {
        /// <summary>
        /// 获取当前时间(yyyy-MM-dd HH:mm:ss)
        /// </summary>
        /// <returns></returns>
        public string getNow()
        {
            return DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        }
        /// <summary>
        /// 获取当前时间(yyyy-MM-dd)
        /// </summary>
        /// <returns></returns>
        public string getNowDate(int days = 0)
        {
            if (days == 0)
            {
                return DateTime.Now.ToString("yyyy-MM-dd");
            }
            else
            {
                return DateTime.Now.AddDays(days).ToString("yyyy-MM-dd");
            }
        }
        /// <summary>
        /// 获取时间字符串(yyyyMMddHHmmss)
        /// </summary>
        /// <returns></returns>
        public string getTimeString()
        {
            return DateTime.Now.ToString("yyyyMMddHHmmss");
        }
        
        /// <summary>
        /// 获取时间字符串(yyyyMMddHHmmssfff)
        /// </summary>
        /// <returns></returns>
        public string getTimesString()
        {
            return DateTime.Now.ToString("yyyyMMddHHmmssfff");
        }
        /// <summary>
        /// 创建检索条件
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public StringBuilder CreateSelectCondition(StringBuilder sb, string condition)
        {
            if (!string.IsNullOrWhiteSpace(condition))
            {
                var json = JsonConvert.DeserializeObject<JObject>(condition);
                foreach (JProperty obj in json.Properties())
                {
                    if (!string.IsNullOrWhiteSpace(obj.Value.ToString()))
                    {
                        if (!string.IsNullOrWhiteSpace(sb.ToString()))
                        {
                            sb.Append(" and ");
                        }
                        sb.Append(obj.Name + " like '%" + obj.Value.ToString() + "%' ");
                    }
                }
            }
            return sb;
        }

        /// <summary>
        /// 创建编码
        /// </summary>
        /// <param name="codeType">数据类型</param>
        /// <param name="memo">备注</param>
        /// <param name="barLength">流水码长度</param>
        /// <returns></returns>
        public string CreateCodeInfo(string codeType, string memo, int barLength,int codeCount=1)
        {
            Tools.TxtLogHelper txt = new Tools.TxtLogHelper();
            CommandService.Commands cmd = new CommandService.Commands();
            try
            {
                if (barLength < 1)
                {
                    barLength = int.Parse(Tools.ConfigReader.Reader["CodeLength"]);
                }
                string date = DateTime.Now.ToString("yy-MM-dd");
                string sql = "select * from sys_SystemCodes_Bank where date='"+ date +"' and tCode='"+ codeType +"'";
                var table = cmd.GetDataTable(sql);
                DbCodeBank code = new DbCodeBank()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  ;
                code.date = date;
                code.tCode = codeType;
                int startNum = 0;
                OperateModel op = new OperateModel();
                if (table.Rows.Count < 1)
                {
                    #region 不存在记录
                    startNum =1;
                    code.memo = memo;
                    code.num = codeCount;
                    op.Types = OperateModel.OperateType.Add;

                    #endregion
                }
                else
                {
                    #region 存在记录
                    startNum = int.Parse(table.Rows[0]["num"].ToString());
                    code =new DbCodeBank();
                    code.Id = int.Parse(table.Rows[0]["id"].ToString());
                    code.num = int.Parse(table.Rows[0]["num"].ToString())+codeCount;
                    op.Types = OperateModel.OperateType.Edit;
                    op.Conditions = "Id";
                    #endregion
                }
                op.Data = code;
                List<OperateModel> list = new List<OperateModel>();
                list.Add(op);
                var r = cmd.MoreUpdate(list);
                if (r.Result)
                {
                    string codes = "";
                    #region 生成唯一标识码
                    for (int i = 1; i <= codeCount; i++)
                    {
                        startNum ++;
                        string newNum = "";
                        newNum.PadLeft(barLength, '0');
                        codes += string.Format("{0}{1}{2},", codeType.ToUpper(), date.Replace("-", ""), newNum);
                    }
                    #endregion
                    return codes.TrimEnd(',');
                    
                }
            }
            catch (Exception ex)
            {
                txt.AddLogMsg("createCode方法异常：" + ex.Message);
            }
            return null;
        }
        /// <summary>
        /// 判断条码是否是箱码
        /// </summary>
        /// <param name="barCode">条码编码</param>
        /// <returns></returns>
        public bool isBoxCode(string barCode)
        {
            CommandService.Commands cmd = new CommandService.Commands();
            string sql = "select top 1 barCode from bar_ProductBoxCode_info where barCode='" + barCode + "'";
            var table = cmd.GetDataTable(sql);
            if (table.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                string defaultQz = Tools.ConfigReader.Reader["BoxPrefix"];
                var qz = barCode.Substring(0, 1);//条码第一位为箱码、条码类别
                if (qz == defaultQz)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 复制实体内容
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public T EntityCopy<T>(object entity)
        {
            return JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(entity));
        }

        //读取excel内容
        public System.Data.DataSet GetExcelData(string excelFilePath)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Sheets sheets;
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
            object oMissiong = System.Reflection.Missing.Value;

            DataSet dataSet = new DataSet();
            string cellContent;
            try
            {
                if (app == null)
                {
                    return null;
                }
                workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                    oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);

                sheets = workbook.Worksheets;
                for (int p = 1; p <= sheets.Count; p++)
                {
                    DataTable dt = new DataTable();
                    Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(p);//读取第一张表

                    for (int j = 1; j <= workSheet.UsedRange.Columns.Count; j++)
                    {

                        Microsoft.Office.Interop.Excel.Range _range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1, j];
                        if (_range.Text.ToString().Trim() == "")
                            dt.Columns.Add("EQUIPMENT");
                        else
                            dt.Columns.Add(_range.Text.ToString().Trim());

                    }
                    for (int i = 2; i <= workSheet.UsedRange.Rows.Count; i++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int j = 1; j <= workSheet.UsedRange.Columns.Count; j++)
                        {
                            Microsoft.Office.Interop.Excel.Range _range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[i, j];
                            cellContent = (_range.Value2 == null) ? "" : _range.Text.ToString().Trim();
                            dr[j - 1] = cellContent;
                        }
                        dt.Rows.Add(dr);
                    }
                    dataSet.Tables.Add(dt);
                }
            }
            finally
            {
                workbook.Close(false, oMissiong, oMissiong);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return dataSet;
        }

        //读取excel内容
        public DataSet ReadExcelToDataSet(string path)
        {
            //连接字符串
            /* 备注：
            	添加 IMEX=1 表示将所有列当做字符串读取，实际应该不是这样，
            	系统默认会查看前8行如果有字符串，则该列会识别为字符串列。
            	如果前8行都是数字，则还是会识别为数字列，日期也一样；
            	如果你觉得8行不够或者太多了，则只能修改注册表HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows，
            	如果此值为0，则会根据所有行来判断使用什么类型，通常不建议这麽做，除非你的数据量确实比较少
            */
            string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;IMEX=1';";

            using (OleDbConnection conn = new OleDbConnection(connstring))
            {
                conn.Open();
                DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });//存放所有的sheet
                DataSet set = new DataSet();
                for (int i = 0; i < sheetsName.Rows.Count; i++)
                {
                    string sheetName = sheetsName.Rows[i][2].ToString();
                    string sql = string.Format("SELECT * FROM [{0}]", sheetName);
                    OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);

                    ada.Fill(set);
                    set.Tables[i].TableName = sheetName;
                }

                return set;

            }
        }
        /// <summary>
        /// 读取excel文件并转为datatable
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public DataTable ReadExcelToDataTable(string path)
        {
            var dataTable = new DataTable();
            Stream stream = null;
            try
            {
                stream = File.OpenRead(path);
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
                HSSFSheet hssfsheet = (HSSFSheet)hssfworkbook.GetSheetAt(hssfworkbook.ActiveSheetIndex);
                HSSFRow hssfrow = (HSSFRow)hssfsheet.GetRow(0);
                int lastCellNum = (int)hssfrow.LastCellNum;
                for (int i = (int)hssfrow.FirstCellNum; i < lastCellNum; i++)
                {
                    DataColumn column = new DataColumn(hssfrow.GetCell(i).StringCellValue);
                    dataTable.Columns.Add(column);
                }
                dataTable.TableName = hssfsheet.SheetName;
                int lastRowNum = hssfsheet.LastRowNum;
                //列名后,从TABLE第二行开始进行填充数据
                for (int i = hssfsheet.FirstRowNum + 1; i <= hssfsheet.LastRowNum; i++)//
                {
                    HSSFRow hssfrow2 = (HSSFRow)hssfsheet.GetRow(i);
                    if(hssfrow2.Cells.Count< lastCellNum)
                    {
                        break;//出现空白单元格情况
                    }
                    DataRow dataRow = dataTable.NewRow();
                    for (int j = (int)hssfrow2.FirstCellNum; j < lastCellNum; j++)//
                    {
                        //如果是公式Cell 
                        //则仅读取其Cell单元格的显示值 而不是读取公式
                        if (hssfrow2.GetCell(j).CellType == CellType.Formula)
                        {
                            hssfrow2.GetCell(j).SetCellType(CellType.String);
                            dataRow[j] = hssfrow2.GetCell(j).StringCellValue;
                        }
                        else
                        {
                            dataRow[j] = hssfrow2.GetCell(j).ToString();
                        }
                        //dataRow[j] = hssfrow2.GetCell(j);//
                    }
                    dataTable.Rows.Add(dataRow);
                }
                stream.Close();
            }
            catch (Exception ex)
            {
                return dataTable;
            }
            return dataTable;
        }
        /// <summary>
        /// 创建列表项样式数据
        /// </summary>
        /// <param name="hList"></param>
        /// <param name="code"></param>
        /// <param name="name"></param>
        /// <param name="type"></param>
        public void createHead(ref List<Dictionary<string, string>> hList, string code, string name, string color = null, string fontSize = null, bool isOver = false, string type = null)
        {
            var dic = new Dictionary<string, string>();
            dic.Add("code", code);
            dic.Add("name", name);
            if (!string.IsNullOrWhiteSpace(type))
            {
                dic.Add("type", type);
            }
            if (!string.IsNullOrWhiteSpace(fontSize))
            {
                dic.Add("fontSize", fontSize);
            }
            if (!string.IsNullOrWhiteSpace(color))
            {
                dic.Add("color", color);
            }
            //因为boolean类型直接转为字符串首字母会大写，所以要在转为小写
            dic.Add("isOver", isOver.ToString().ToLower());
            hList.Add(dic);
        }

        /// <summary>
        /// 检查一个字符串是否是纯数字构成的，一般用于查询字符串参数的有效性验证。(0除外)
        /// </summary>
        /// <param name="_value">需验证的字符串。。</param>
        /// <returns>是否合法的bool值。</returns>
        public bool IsNumberId(string _value)
        {
            return QuickValidate(@"^(-?\d+)(\.\d+)?$", _value);
        }

        /// <summary>
        /// 快速验证一个字符串是否符合指定的正则表达式。
        /// </summary>
        /// <param name="_express">正则表达式的内容。</param>
        /// <param name="_value">需验证的字符串。</param>
        /// <returns>是否合法的bool值。</returns>
        public bool QuickValidate(string _express, string _value)
        {
            if (_value == null) return false;
            Regex myRegex = new Regex(_express);
            if (_value.Length == 0)
            {
                return false;
            }
            return myRegex.IsMatch(_value);
        }
    }
}
